








/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dto.ArticleDTO;
import java.beans.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import util.DBConfig;

/**
 *
 * @author vodangbinhtay
 */
public class ArticleDAO {
    DBConfig con;
    Connection connect;
    ArrayList<ArticleDTO> list = null;
    String SQL_CREATE = "INSERT INTO Articles VALUES(?,?,?,?,?,?)";
    String SQL_READALL = "SELECT * FROM Articles";
    String SQL_READBYID = "SELECT * FROM Articles WHERE ArtID = ?";
    String SQL_READBYNAME = "SELECT * FROM Articles WHERE Name = ?";
    String SQL_UPDATE = "UPDATE Articles SET AccID=?, Name=?,TypeID=?,Content=?,Description=?,DateCreate=? WHERE ArtID=?";
    String SQL_DELETE = "DELETE Articles WHERE ArtID=?";

    public ArticleDAO() {
        con = new DBConfig();
        list = new ArrayList<ArticleDTO>();
    }

    public ArticleDTO create(ArticleDTO dto) {
        try {
            connect = con.OpenConnect();
            PreparedStatement pst = connect.prepareStatement(SQL_CREATE);
            pst.setInt(1, dto.getAccid());
            pst.setString(2, dto.getName());
            pst.setInt(3, dto.getTypeid());
            pst.setString(4, dto.getContent());
            pst.setString(5, dto.getDescription());
            pst.setDate(6, dto.getDatecreate());
            
            pst.execute();
            return dto;

        } catch (SQLException ex) {
            Logger.getLogger(LoginDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            con.CloseConnect(connect);
        }
    }

    public ArrayList<ArticleDTO> realAll() {
        try {
            connect = con.OpenConnect();
            java.sql.Statement st = connect.createStatement();
            ResultSet rs = st.executeQuery(SQL_READALL);
            if (rs != null) {
                while (rs.next()) {
                    ArticleDTO login = new ArticleDTO(rs.getInt(1), rs.getInt(2), rs.getString(3),rs.getInt(4),rs.getString(5),rs.getString(6),rs.getDate(7));
                    list.add(login);
                }
                return list;
            }
            return null;
        } catch (SQLException ex) {
            Logger.getLogger(LoginDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            con.CloseConnect(connect);
        }

    }

    public ArticleDTO readByID(int id) {
        try {
            connect = con.OpenConnect();
            PreparedStatement pst = connect.prepareStatement(SQL_READBYID);
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();
            if (rs != null) {
                if (rs.next()) {
                    ArticleDTO dto = new ArticleDTO(rs.getInt(1), rs.getInt(2), rs.getString(3),rs.getInt(4),rs.getString(5),rs.getString(6),rs.getDate(7));
                    return dto;
                }
            }
            return null;
        } catch (SQLException ex) {
            Logger.getLogger(LoginDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            con.CloseConnect(connect);
        }
    }
    
    public ArticleDTO readByUserName(String uName) {
        try {
            connect = con.OpenConnect();
            PreparedStatement pst = connect.prepareStatement(SQL_READBYNAME);
            pst.setString(1, uName);
            ResultSet rs = pst.executeQuery();
            if (rs != null) {
                if (rs.next()) {
                    ArticleDTO login = new ArticleDTO(rs.getInt(1), rs.getInt(2), rs.getString(3),rs.getInt(4),rs.getString(5),rs.getString(6),rs.getDate(7));
                    return login;
                }
            }
            return null;
        } catch (SQLException ex) {
            Logger.getLogger(LoginDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            con.CloseConnect(connect);
        }
    }

    public int update(ArticleDTO dto) {
        try {
            connect = con.OpenConnect();
            PreparedStatement pst;
            pst = connect.prepareStatement(SQL_UPDATE);
            
             pst.setInt(1, dto.getAccid());
            pst.setString(2, dto.getName());
            pst.setInt(3, dto.getTypeid());
            pst.setString(4, dto.getContent());
            pst.setString(5, dto.getDescription());
            pst.setDate(6, dto.getDatecreate());
            return pst.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(LoginDAO.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        } finally {
            con.CloseConnect(connect);
        }

    }

    public int delete(int id) {
        try {
            connect = con.OpenConnect();
            PreparedStatement pst;
            pst = connect.prepareStatement(SQL_DELETE);
            pst.setInt(1, id);
            int r = pst.executeUpdate();
            return r;
        } catch (SQLException ex) {
            Logger.getLogger(ArticleDAO.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        } finally {
            con.CloseConnect(connect);
        }
    }

//    public static void main(String[] args) {
//        ArticleDAO dao = new ArticleDAO();
//        ArticleDTO dto = new ArticleDTO(2, "veve", "Hoahoa");
//        ArticleDTO l = dao.create(dto);
////        dto.setPass("1111");
////        dto.setUser("uuu");
////        int r = dao.update(dto);
////        LoginDTO l = dao.readByID(2);
////        l = dao.readByUserName("uuu");
//        int rs = dao.delete(6);
//        //dao.delete();
//    }
}
